package no.aina.skandiabanken;

import java.util.ArrayList;
import java.util.Collections;
import java.util.HashSet;
import java.util.List;
import java.util.Set;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteException;
import android.database.sqlite.SQLiteOpenHelper;
import android.database.sqlite.SQLiteQueryBuilder;
import android.provider.BaseColumns;

public class Accounts extends SQLiteOpenHelper {

	private static final String DATABASE_NAME = "skandiabanken.db";
	private static final int DATABASE_VERSION = 1;

	static final String TABLE_NAME_ACCOUNTS = "accounts";
	static final String TABLE_NAME_SETTINGS = "settings";

	//	private boolean created;
	//	private static boolean hasNew = false;

	private static SQLiteDatabase dbReadable;
	private static SQLiteDatabase dbWritable;

	public Accounts(Context context) {
		super(context, DATABASE_NAME, null, DATABASE_VERSION);
	}

	@Override
	public void onCreate(SQLiteDatabase db) {
		db.execSQL("CREATE TABLE IF NOT EXISTS " + TABLE_NAME_ACCOUNTS + " ("
				+ AccountColumns._ID + " INTEGER PRIMARY KEY,"
				+ AccountColumns.NAME + " TEXT,"
				+ AccountColumns.ACCOUNT_NUMBER + " TEXT,"
				+ AccountColumns.AMOUNT + " INTEGER,"
				+ AccountColumns.LAST_CHECKED + " TEXT"
				+ ");");

		db.execSQL("CREATE TABLE IF NOT EXISTS " + TABLE_NAME_SETTINGS + " ("
				+ SettingsColumns._ID + " INTEGER PRIMARY KEY,"
				+ SettingsColumns.NAME + " TEXT,"
				+ SettingsColumns.VALUE + " TEXT"
				+ ");");


		//		hasNew = true;
	}

	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {

		Set<Account> accounts = Collections.emptySet();
		accounts = getAccountsTable(db);
		db.execSQL("DROP TABLE IF EXISTS " + TABLE_NAME_ACCOUNTS);

		Set<Setting> settings = Collections.emptySet();
		settings = getSettingsTable(db);
		db.execSQL("DROP TABLE IF EXISTS " + TABLE_NAME_SETTINGS);

		onCreate(db);

		dbWritable = db;

		saveAccounts(new ArrayList<Account>(accounts));

		for (Setting setting : settings) {
			saveSetting(setting.name, setting.value);
//			hasNew = true;
		}

		//		hasNew = true;
		dbWritable = null;
	}



	private Set<Setting> getSettingsTable(SQLiteDatabase db) {

		Set<Setting> settings = new HashSet<Setting>();

		try {

			SQLiteQueryBuilder qb = new SQLiteQueryBuilder();

			qb.setTables(TABLE_NAME_SETTINGS);
			qb.setDistinct(true);

			Cursor c = qb.query(db, null, null, null, null, null, null);

			while (c.moveToNext()) {
				Setting setting = new Setting();
				setting.id = c.getInt(0);
				setting.name = c.getString(1);
				setting.value = c.getString(2);

				settings.add(setting);
			}
			c.close();

		} catch (SQLiteException e) {}

		return settings;
	}

	private Set<Account> getAccountsTable(SQLiteDatabase db) {

		Set<Account> accounts = new HashSet<Account>();

		try {
			SQLiteQueryBuilder qb = new SQLiteQueryBuilder();

			qb.setTables(TABLE_NAME_ACCOUNTS);
			qb.setDistinct(true);

			Cursor c = qb.query(db, null, null, null, null, null, null);

			while (c.moveToNext()) {
				Account p = getAccount(c);

				accounts.add(p);
			}
			c.close();

		} catch (SQLiteException e) {}

		return accounts;
	}

	//	private void emptyAccounts() {
	//
	//		hasNew = true;
	//
	//		if (dbWritable == null) {
	//			dbWritable = getWritableDatabase();
	//		}
	//
	//		dbWritable.delete(TABLE_NAME_ACCOUNTS, null, null);
	//	}

	private void saveAccounts(List<Account> accounts) {

		if (dbWritable == null) {
			dbWritable = getWritableDatabase();
		}

		synchronized (dbWritable) {

			dbWritable.beginTransaction();
			try {

				for (Account p : accounts) {
					//					hasNew = true;
					saveAccount(p);
				}

				dbWritable.setTransactionSuccessful();
			} finally {
				if (dbWritable.inTransaction()) {
					dbWritable.endTransaction();
				}
			}
		}

	}

	private Account getAccount(Cursor c) {
		Account p = new Account();
		p.id = c.getInt(0);
		p.name = c.getString(1);
		p.accountNumber = c.getString(2);
		p.amount = c.getInt(3);
		p.lastChecked = c.getString(4);

		return p;
	}

	String getSetting(String name, String defaultValue) {

		String value = defaultValue;

		if (dbReadable == null) {
			dbReadable = getReadableDatabase();
		}

		SQLiteQueryBuilder qb = new SQLiteQueryBuilder();

		qb.setTables(TABLE_NAME_SETTINGS);
		qb.appendWhere(SettingsColumns.NAME + " = '" + name + "'");
		qb.setDistinct(true);

		Cursor c = qb.query(dbReadable, new String[]{SettingsColumns.VALUE}, null, null, null, null, null);

		if (c.moveToNext()) {
			value = c.getString(0);
		}
		c.close();

		return value;
	}

	void saveSetting(String name, String value) {

		if (dbWritable == null) {
			dbWritable = getWritableDatabase();
		}

		ContentValues values = new ContentValues();
		values.put(SettingsColumns.NAME, name);
		values.put(SettingsColumns.VALUE, value);

		if (hasSetting(name)) {
			@SuppressWarnings("unused")
			long id = dbWritable.update(TABLE_NAME_SETTINGS, values, SettingsColumns.NAME + " = ?", new String[]{name});
		} else {
			@SuppressWarnings("unused")
			long id = dbWritable.insert(TABLE_NAME_SETTINGS, null, values);
		}
	}

	private boolean hasSetting(String name) {

		if (dbReadable == null) {
			dbReadable = getReadableDatabase();
		}

		SQLiteQueryBuilder qb = new SQLiteQueryBuilder();

		qb.setTables(TABLE_NAME_SETTINGS);
		qb.appendWhere(SettingsColumns.NAME + " = '" + name + "'");
		qb.setDistinct(true);

		Cursor c = qb.query(dbReadable, null, null, null, null, null, null);

		if (c.moveToNext()) {
			c.close();
			return true;
		}

		c.close();
		return false;
	}

	private class AccountColumns implements BaseColumns {
		private static final String LAST_CHECKED = "lastchecked";
		private static final String AMOUNT = "amount";
		private static final String NAME = "name";
		private static final String ACCOUNT_NUMBER = "accountnumber";

		private AccountColumns() {}
	}

	private class SettingsColumns implements BaseColumns {
		private static final String NAME = "name";
		private static final String VALUE = "value";

		private SettingsColumns() {}
	}

	private class Setting {

		private String name;
		private String value;
		@SuppressWarnings("unused")
		private int id;

	}

	//	private static boolean hasNewAccounts() {
	//		return hasNew;
	//	}


	Set<Account> getAccounts() {

		if (dbReadable == null) {
			dbReadable = getReadableDatabase();
		}

		Set<Account> accounts = new HashSet<Account>();

		SQLiteQueryBuilder qb = new SQLiteQueryBuilder();

		qb.setTables(TABLE_NAME_ACCOUNTS);

		qb.setDistinct(true);

		Cursor c = qb.query(dbReadable, null, null, null, null, null, null);

		while (c.moveToNext()) {
			Account p = getAccount(c);
			accounts.add(p);
		}
		c.close();

		return accounts;
	}

	//	private Account getAccount(int id) throws Exception {
	//
	//		if (dbReadable == null) {
	//			dbReadable = getReadableDatabase();
	//		}
	//
	//		SQLiteQueryBuilder qb = new SQLiteQueryBuilder();
	//
	//		qb.setTables(TABLE_NAME_ACCOUNTS);
	//
	//		qb.appendWhere(AccountColumns._ID + " = " + id + "");
	//		qb.setDistinct(true);
	//
	//		Cursor c = qb.query(dbReadable, null, null, null, null, null, null);
	//
	//		if (c.moveToNext()) {
	//
	//			Account p = getAccount(c);
	//
	//			c.close();
	//
	//			return p;
	//		}
	//		c.close();
	//
	//		throw new Exception("Account not found");
	//
	//	}

	@SuppressWarnings("boxing")
	int saveAccount(Account p) {
		ContentValues values = new ContentValues();
		values.put(AccountColumns.NAME, p.name);
		values.put(AccountColumns.ACCOUNT_NUMBER, p.accountNumber);
		values.put(AccountColumns.AMOUNT, p.amount);
		values.put(AccountColumns.LAST_CHECKED, p.lastChecked);

		if (dbWritable == null) {
			dbWritable = getWritableDatabase();
		}

		//		hasNew = true;

		int id = (int) dbWritable.insert(TABLE_NAME_ACCOUNTS, null, values);

		return id;
	}

	public void deleteAccount(int id) {

		if (dbWritable == null) {
			dbWritable = getWritableDatabase();
		}

		@SuppressWarnings("unused")
		long deleted = dbWritable.delete(TABLE_NAME_ACCOUNTS, AccountColumns._ID + " = ?", new String[] {""+id});
	}

}
